﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;

namespace WebQueryRole
{
    public class DBCollection
    {
        /// <summary>
        ///  Connection to SQL Azure
        /// </summary>

        private Dictionary<string, SqlConnection> _sqlConnections;
        private Dictionary<string, string> _sqlConnectionStrings;

        public DBCollection()
        {
            _sqlConnections = new Dictionary<string, SqlConnection>();
            _sqlConnectionStrings = new Dictionary<string, string>();

            _sqlConnectionStrings["data"] = "Server=tcp:ec2-184-72-5-110.us-west-1.compute.amazonaws.com;User ID=web_user;Password=eScience4All!;Trusted_Connection=False;Database = BluePlacerDB;";
            _sqlConnectionStrings["web"] = "Server=tcp:ec2-184-72-5-110.us-west-1.compute.amazonaws.com;User ID=web_user;Password=eScience4All!;Trusted_Connection=False;Database = WebDB;";
            //_sqlConnectionStrings["web"] = "Server=tcp:u7t1o5e5s7.database.windows.net;User ID=sciflex;Password=eScience4All!;Trusted_Connection=False;Database = WebDB;";
        }

        /// <summary>
        /// Get database connection for this database string
        /// </summary>
        /// <param name="databaseName">name of database in SQL Azure</param>
        /// <returns></returns>
        public SqlConnection this[string databaseName]
        {
            get
            {
                databaseName = databaseName.ToLower();  // keep lower case in dictionary, sql isnt case sensitive
                if (! _sqlConnections.ContainsKey(databaseName))
                {
                    if (!_sqlConnectionStrings.ContainsKey(databaseName))
                    {
                        return null;
                    }
                    string connStr = _sqlConnectionStrings[databaseName];
                    _sqlConnections[databaseName] = NewConnection(connStr);
                }                

                return _sqlConnections[databaseName];
            }
        }

        /// <summary>
        /// Close all database connections. 
        /// </summary>
        public void CloseAll()
        {
            foreach(SqlConnection conn in _sqlConnections.Values) {
                if (conn != null)
                {
                    conn.Close();
                }
            }
        }

        /// <summary>
        /// Lazy initialization for SQL connection 
        /// </summary>
        private SqlConnection NewConnection(String connString)
        {            
            // This example needs the 
            // System.Data.SqlClient library
            //string ConnectionString = "Server=tcp:u7t1o5e5s7.database.windows.net;Database=AzureTest;User ID=sciflex;Password=eScience4All!;Trusted_Connection=False;";
            //string ConnectionString = @"Server=(local)\SQLExpress;Database=COG;Trusted_Connection=True;";
            
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = connString;
                conn.Open();

                // You can get the server version 
                // SQLConnection.ServerVersion
            }
            catch (Exception Ex)
            {
                // Try to close the connection
                if (conn != null)
                    conn.Dispose();

                // Create a (useful) error message
                string ErrorMessage = "A error occurred while trying to connect to the server.";
                ErrorMessage += Environment.NewLine;
                ErrorMessage += Environment.NewLine;
                ErrorMessage += Ex.Message;

                // Show error message (this = the parent Form object)
                //MessageBox.Show(this, ErrorMessage, "Connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                Console.WriteLine("Connection Error: \n\r" + ErrorMessage);

                // Stop here
                return null;
            }
            return conn;
        }         
    }
}
